package com.example.yls.sql_listview;

import android.content.ContentValues;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.text.Editable;
import android.text.TextWatcher;
import android.view.View;
import android.widget.AdapterView;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ImageView;
import android.widget.ListView;

import java.io.File;
import java.util.ArrayList;

public class MainActivity extends AppCompatActivity implements IOnDataChangeListener {
    private ListView lvStu;
    private Button btnAdd;
    private ArrayList<Student> studentArrayList=new ArrayList<>();
    private StuAdapter adapter;
    private EditText edtKey;
    private ImageView ivSearch;
    private MySqliteHelper helper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        helper=new MySqliteHelper(MainActivity.this,"stu.db",null,1);

        findViews();
        //initStudentArrayList();

        adapter=new StuAdapter(MainActivity.this,studentArrayList,MainActivity.this);
        readDataFromDB();

        lvStu.setAdapter(adapter);
        lvStu.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
                Student student=studentArrayList.get(i);
                Intent intent=new Intent();
                intent.setClass(MainActivity.this,EditActivity.class);
                intent.putExtra("STUDENT",student);
                startActivityForResult(intent, 1002);
            }
        });
    }

    private void initStudentArrayList() {
        studentArrayList.add(new Student("17001","lisa",18));
        studentArrayList.add(new Student("17002","lier",18));
        studentArrayList.add(new Student("17003","liwu",18));
        studentArrayList.add(new Student("17004","liqi",18));
    }

    /**/
    private void findViews() {
        lvStu=findViewById(R.id.lv_stu);
        btnAdd=findViewById(R.id.btn_add);

        btnAdd.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Intent intent=new Intent();
                intent.setClass(MainActivity.this,AddActivity.class);
                startActivityForResult(intent,1001);
            }
        });
        edtKey=findViewById(R.id.edt_key);
        ivSearch=findViewById(R.id.iv_search);
        ivSearch.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String key=edtKey.getText().toString();
                searchFromDB(key);
            }
        });
        edtKey.addTextChangedListener(new TextWatcher() {
            @Override
            public void beforeTextChanged(CharSequence charSequence, int i, int i1, int i2) {

            }

            @Override
            public void onTextChanged(CharSequence charSequence, int i, int i1, int i2) {

            }

            @Override
            public void afterTextChanged(Editable editable) {
                String key=edtKey.getText().toString();
                searchFromDB(key);
            }
        });
    }

    /**
     * 从数据库读取学生列表
     */
    private void readDataFromDB() {
        SQLiteDatabase db = helper.getReadableDatabase();

        studentArrayList.clear();

        Cursor cursor=db.query("student",null,null,
                null,null,null,null);

        if(cursor != null && cursor.getCount() > 0){
            for(cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()){
                String stuno = cursor.getString(0);
                String name = cursor.getString(1);
                int age = cursor.getInt(2);

                Student  student = new Student(stuno, name, age);
                studentArrayList.add(student);
            }
        }

        adapter.notifyDataSetChanged();

        cursor.close();
        db.close();
    }

    /**
     * 从数据库搜索学生列表
     */
    private void searchFromDB(String key) {
        SQLiteDatabase db = helper.getReadableDatabase();

        String where="name like '%"+key+"%' OR stuno like '%"+ key+"%'"+" OR age='"+key+"'";

        studentArrayList.clear();

        Cursor cursor = db.query("student", null, where,
                null,null,null,null,null);

        if(cursor != null && cursor.getCount() > 0){
            for(cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()){
                String stuno = cursor.getString(0);
                String name = cursor.getString(1);
                int age = cursor.getInt(2);

                Student  student = new Student(stuno, name, age);
                studentArrayList .add(student);
            }
        }

        adapter.notifyDataSetChanged();

        cursor.close();
        db.close();
    }

    protected void onActivityResult(int requestCode,int resultCode,Intent data){
        if(resultCode==2001){
            String stuno=data.getStringExtra("STUNO");
            String name=data.getStringExtra("NAME");
            int age=data.getIntExtra("AGE",0);

            Student student=new Student(stuno,name,age);
            addStudentToDB(student);
        }else if(resultCode==3001){
            Student student = (Student) data.getSerializableExtra("STUDENT");
            updateStudentToDB(student);
        }
    }

    private void updateStudentToDB(Student student) {
        SQLiteDatabase db = helper.getWritableDatabase();

        //第一种 组装语句
        /*String sql="update student set name = '"+student.getName()+"'," +
                " age = "+student.getAge()+" where stuno = '"+student.getStuno()+"'";
        db.execSQL(sql);
        db.close();
        readDataFromDB();*/

        //第二种
        String where="stuno = '"+student.getStuno()+"'";
        ContentValues values=new ContentValues();
        values.put("name",student.getName());
        values.put("age",student.getAge());

        db.update("student",values,where,null);
        db.close();
        readDataFromDB();
    }

    private void addStudentToDB(Student student) {
        SQLiteDatabase db = helper.getWritableDatabase();

        ContentValues values=new ContentValues();
        values.put("stuno",student.getStuno());
        values.put("name",student.getName());
        values.put("age",student.getAge());

        db.insert("student",null,values);
        db.close();
        readDataFromDB();
    }

    public void del(Student student){
        SQLiteDatabase db = helper.getWritableDatabase();

        /*第一种 组装删除语句*/
        /*String sql = "delete from student where stuno = '"+student.getStuno()+"'";
        db.execSQL(sql);
        db.close();
        readDataFromDB();*/

        /*第二种 使用delete接口*/
        /*String where = "stuno = '" + student.getStuno() + "'";
        db.delete("student", where, null);
        db.close();
        readDataFromDB();*/

        /*第三种 使用带参数接口*/
        String where="stuno=? AND name=?";
        String[] args={student.getStuno(),student.getName()};
        db.delete("student",where,args);
        db.close();
        readDataFromDB();

    }

}
